package yiguan;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Properties;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.ResourceBundle;
import java.util.Calendar;
import java.util.Date;
import java.io.PrintWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.json.JSONObject;
import org.json.JSONArray;
import org.json.JSONException;
import org.apache.log4j.Logger;

public abstract class DBUtil {
	private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
	private static final String DBURL = "jdbc:derby:CanDB";
	private static Logger logger = Logger.getLogger("DBUtil");
	private static Properties bundleMsg = new Properties();

	public static final java.text.SimpleDateFormat TIME_FORMAT = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	public static final java.text.SimpleDateFormat DATE_FORMAT = new java.text.SimpleDateFormat("yyyyMMdd");
	public static final java.text.SimpleDateFormat MONTH_FORMAT = new java.text.SimpleDateFormat("yyyyMM");
	public static final String[] ALLOC_MONTH = {"01", "04", "07", "10"};
	public static final String THIS_YEAR = String.valueOf(Calendar.getInstance().get(Calendar.YEAR));

	static {
		try {
  			bundleMsg.load(XingServlet.class.getResourceAsStream("Msg.properties"));
  		} catch (Exception e) {}
	}

	/*
	** Actions of Task
	*/
	//pick up task
	public static void pickupTask(String imei, String taskid, String cid) {
		Waiter waiter = Waiter.getWaiterByImei(imei);
		int tid;

		tid = Integer.parseInt(taskid);
		Task task = Task.getAssignedTaskByCustomer(waiter.getMobile(), cid, tid);

		if (task != null)
			task.setPickupState();  //set task with pickup state

	}

	public static String finishTask(String imei, String taskid, Customer customer, int payment, int cash) {
		Waiter waiter = Waiter.getWaiterByImei(imei);
		int tid;

		tid = Integer.parseInt(taskid);
		Task task = Task.getPickupedTaskByCustomer(waiter.getMobile(), customer.getCid(), tid);
		if (task == null)	//invalid of pay again
			return null;

		Connection conn = null;
		try {
			conn = openConnection();
			conn.setAutoCommit(false);  //Important
			task.setFinishState(conn, payment, cash);  //set task with finish state
			logFlow(conn, task, customer, payment, cash); //log the flow
			customer.pay(conn, payment);  //change the sum of account
			conn.commit();
		} catch(SQLException e) {
			try {
				conn.rollback();
			} catch(SQLException e1) {}
			logger.error(e.getMessage(), e);
		} finally {
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return task.getTaskid();
	}

	public static int withdrawTask(String imei, String taskid) {
		int retval = -1;

		Waiter waiter = Waiter.getWaiterByImei(imei);
		Task task = Task.getFinishedTaskByID(taskid);

		if (task == null)
			return 1;	//invalid taskid

		if (!task.getSmobile().equals(waiter.getMobile()))
			return 2;	//not assigned waiter

		Customer customer = Customer.getCustomerByID(task.getCid());
		Connection conn = null;
		try {
			conn = openConnection();
			conn.setAutoCommit(false);  //Important
			task.setWithdrawState(conn);
			logFlow(conn, task, customer, -task.getConsume(), -task.getCash());
			customer.withdraw(conn, task.getConsume());
			conn.commit();
			retval = 0;
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch(SQLException e1) {}
			logger.error(e.getMessage(), e);
		} finally {
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public static JSONObject flowAll(int page, Hashtable params) {
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int total;
		int offset = page * 20 - 20;
		JSONObject retval = new JSONObject();
		JSONArray flows = new JSONArray();

		String sqlSelect = "select * from accflow";
		String sqlWhere = buildSqlWhere(params);
		String sql = sqlSelect + sqlWhere + " order by flowid desc offset ? rows fetch next 20 rows only";

		try {
			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select count(*) from accflow" + sqlWhere);
			rs.next();
			total = rs.getInt(1);
			rs.close();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, offset);
			rs = ps.executeQuery();
			while(rs.next()) {
				JSONObject flow = new JSONObject();
				flow.put("id", rs.getString("flowid"));
				String[] ss = new String[16];
				ss[0] = rs.getString("flowid");
				ss[1] = rs.getString("cid").trim();
				ss[2] = rs.getString("cname").trim();
				ss[3] = rs.getString("ctype");
				ss[4] = rs.getString("taskid");
				ss[5] = rs.getString("csex");
				ss[6] = rs.getString("cservice");
				ss[7] = rs.getString("district");
				ss[8] = rs.getString("oldsum");
				ss[9] = rs.getString("cursum");
				ss[10] = rs.getString("consume");
				ss[11] = rs.getString("cash");
				ss[12] = rs.getString("wname").trim();
				ss[13] = rs.getString("comid");
				ss[14] = rs.getString("recdate");
				ss[15] = TIME_FORMAT.format(rs.getTimestamp("rectime"));
				flow.put("cell", ss);
				flows.put(flow);
			}
			retval.put("page", page);
			retval.put("total",(total / 20) + 1);
			retval.put("records", total);
			retval.put("rows", flows);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(JSONException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public static JSONObject flowSum(int page, Hashtable params) {
		Properties allStreet = BaseServlet.getAllStreet();
		Properties allCommunity = BaseServlet.getAllCommunity();
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int total;
		int offset = page * 20 - 20;
		//int n = 0;
		JSONObject retval = new JSONObject();
		JSONObject userData = new JSONObject();
		JSONArray flows = new JSONArray();

		String sqlSelect = "select cid,cname,ctype,count(distinct taskid) servnum,district,sum(consume) consume,sum(cash) cash from accflow";
		String sqlWhere = buildSqlWhere(params);
		String sql = sqlSelect + sqlWhere + " group by cid,cname,ctype,district";

		try {
			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select count(distinct cid) from accflow" + sqlWhere);
			rs.next();
			total = rs.getInt(1);
			rs.close();
			rs = stmt.executeQuery("select count(distinct taskid),sum(consume),sum(cash) from accflow" + sqlWhere);
			rs.next();
			userData.put("servnum", bundleMsg.getProperty("SERVICE_COUNT") + " : " + rs.getInt(1));
			userData.put("consume", bundleMsg.getProperty("CONSUME_SUM") + " : " + rs.getInt(2));
			userData.put("cash", bundleMsg.getProperty("CASH_SUM") + " : " + rs.getInt(3));
			rs.close();
			ps = conn.prepareStatement("select a.cid,a.cname,a.ctype,a.servnum,a.district,a.consume,a.cash,c.sid,c.commuid,c.caddress,c.cmobile,c.ctel from (" +
				sql + ") as a, customer c where a.cid=c.cid offset ? rows fetch next 20 rows only");
			ps.setInt(1, offset);
			rs = ps.executeQuery();
			while(rs.next()) {
				JSONObject flow = new JSONObject();
				flow.put("id", rs.getString("cid"));
				String[] ss = new String[11];
				ss[0] = rs.getString("cid").trim();
				ss[1] = rs.getString("cname").trim();
				ss[2] = rs.getString("ctype");
				ss[3] = rs.getString(4);
				ss[4] = rs.getString("district");
				ss[5] = rs.getString(6);
				ss[6] = rs.getString(7);
				ss[7] = allStreet.getProperty(rs.getString(8));
				ss[8] = allCommunity.getProperty(rs.getString(9));
				ss[9] = rs.getString(10);
				ss[10] = rs.getString(11) + (rs.getString(11) == null? "" : " " + rs.getString(12));
				//n += rs.getInt(4);
				flow.put("cell", ss);
				flows.put(flow);
			}
			//userData.put("servnum", bundleMsg.getProperty("SERVICE_COUNT") + " : " + n);
			retval.put("page", page);
			retval.put("total",(total / 20) + 1);
			retval.put("records", total);
			retval.put("rows", flows);
			retval.put("userdata", userData);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(JSONException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}
	
	public static void flowsumExcel(Hashtable params, HttpServletResponse response) {
		Properties allStreet = BaseServlet.getAllStreet();
		Properties allCommunity = BaseServlet.getAllCommunity();
		PrintWriter out;
		Properties bundle = new Properties();
		StringBuffer sb = new StringBuffer();
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		int total;
		int servnum = 0, consumesum = 0, cashsum=0;

		String sqlSelect = "select cid,cname,ctype,count(distinct taskid) servnum,district,sum(consume) consume,sum(cash) cash from accflow";
		String sqlWhere = buildSqlWhere(params);
		String sql = sqlSelect + sqlWhere + " group by cid,cname,ctype,district";

		response.reset();
		response.setHeader("Content-Disposition", "attachment; filename=" + System.currentTimeMillis() + ".csv");
		response.setContentType("application/vnd.ms-excel; charset=GBK");
		try {
			out = response.getWriter();
			bundle.load(Customer.class.getResourceAsStream("Customer.properties"));
			for (int i = 1; i <= 10; i++) {
				sb.append(bundle.getProperty("sum" + i)).append(",");
			}
			sb.append(bundle.getProperty("sum11"));
			out.println(sb.toString());
			
			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select a.cid,a.cname,a.ctype,a.servnum,a.district,a.consume,a.cash,c.sid,c.commuid,c.caddress,c.cmobile,c.ctel from (" +
				sql + ") as a, customer c where a.cid=c.cid");
			while(rs.next()) {
				sb = new StringBuffer();
				sb.append("\t");
				sb.append(rs.getString("cid").trim()).append(",");
				sb.append(rs.getString("cname").trim()).append(",");
				sb.append(bundle.getProperty("ctype" + rs.getString("ctype"))).append(",");
				sb.append(rs.getString(4)).append(",");
				sb.append(bundle.getProperty("district" + rs.getString("district"))).append(",");
				sb.append(rs.getString(6)).append(",");
				sb.append(rs.getString(7)).append(",");
				sb.append(allStreet.getProperty(rs.getString(8))).append(",");
				sb.append(allCommunity.getProperty(rs.getString(9))).append(",");
				sb.append(rs.getString(10)).append(",");
				sb.append(rs.getString(11) + (rs.getString(11) == null? "" : " " + rs.getString(12)));
				servnum += rs.getInt(4);
				consumesum += rs.getInt(6);
				cashsum += rs.getInt(7); 
				out.println(sb.toString());
			}
			
			out.println();
			out.println();
			out.print(",,,");
			out.print(bundle.getProperty("servnum") + servnum);
			out.print(",,");
			out.print(bundle.getProperty("consumesum") + consumesum);
			out.print(",");
			out.println(bundle.getProperty("cashsum") + cashsum);
			out.flush();
			out.close();
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(IOException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	public static JSONObject ledgerStat(int page, Hashtable params) {
		Properties allStreet = BaseServlet.getAllStreet();
		Properties allCommunity = BaseServlet.getAllCommunity();
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int total;
		int offset = page * 20 - 20;
		//int n = 0;
		JSONObject retval = new JSONObject();
		JSONObject userData = new JSONObject();
		JSONArray flows = new JSONArray();

		String sqlSelect = "select cid,cname,ctype,count(distinct taskid) servnum,sum(consume) consume,district from accflow";
		String sqlWhere = buildSqlWhere(params);
		String sql = sqlSelect + sqlWhere + " group by cid,cname,ctype,district";

		try {
			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select count(distinct cid) from accflow" + sqlWhere);
			rs.next();
			total = rs.getInt(1);
			rs.close();
			rs = stmt.executeQuery("select sum(consume),count(distinct taskid) from accflow" + sqlWhere);
			rs.next();
			userData.put("consume", bundleMsg.getProperty("CONSUME_SUM") + " : " + (rs.getString(1) == null?"0":rs.getString(1)));
			userData.put("servnum", bundleMsg.getProperty("SERVICE_COUNT") + " : " + (rs.getString(2) == null?"0":rs.getString(2)));
			rs.close();
			ps = conn.prepareStatement("select a.cid,a.cname,a.ctype,a.servnum,a.consume,a.district,c.sid,c.commuid,c.caddress,c.cmobile,c.ctel from (" +
				sql + ") as a, customer c where a.cid=c.cid offset ? rows fetch next 20 rows only");
			ps.setInt(1, offset);
			rs = ps.executeQuery();
			while(rs.next()) {
				JSONObject flow = new JSONObject();
				flow.put("id", rs.getString(1));
				String[] ss = new String[10];
				ss[0] = rs.getString(1);
				ss[1] = rs.getString(2).trim();
				ss[2] = rs.getString(3);
				ss[3] = rs.getString(4);
				ss[4] = rs.getString(5);
				ss[5] = rs.getString(6);
				ss[6] = allStreet.getProperty(rs.getString(7));
				ss[7] = allCommunity.getProperty(rs.getString(8));
				ss[8] = rs.getString(9);
				ss[9] = rs.getString(10) + (rs.getString(11) == null? "" : " " + rs.getString(11));
				//n += rs.getInt(3);
				flow.put("cell", ss);
				flows.put(flow);
			}

			retval.put("page", page);
			retval.put("total",(total / 20) + 1);
			retval.put("records", total);
			retval.put("rows", flows);
			retval.put("userdata", userData);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(JSONException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public static void ledgerExcel(Hashtable params, HttpServletResponse response) {
		Properties allStreet = BaseServlet.getAllStreet();
		Properties allCommunity = BaseServlet.getAllCommunity();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		PrintWriter out;
		Properties bundle = new Properties();
		StringBuffer sb = new StringBuffer();
		int total, consumesum, servnum;

		String sqlSelect = "select cid,cname,ctype,count(distinct taskid) servnum,sum(consume) consume,district from accflow";
		String sqlWhere = buildSqlWhere(params);
		String sql = sqlSelect + sqlWhere + " group by cid,cname,ctype,district";

		response.reset();
		response.setHeader("Content-Disposition", "attachment; filename=" + System.currentTimeMillis() + ".csv");
		response.setContentType("application/vnd.ms-excel; charset=GBK");
		try {
			out = response.getWriter();
			bundle.load(Customer.class.getResourceAsStream("Customer.properties"));
			for (int i = 1; i <= 10; i++) {
				sb.append(bundle.getProperty("ledgecol" + i));
				if (i < 10)
					sb.append(",");
			}
			out.println(sb.toString());
			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select count(distinct cid) from accflow" + sqlWhere);
			rs.next();
			total = rs.getInt(1);
			rs.close();
			rs = stmt.executeQuery("select sum(consume),count(distinct taskid) from accflow" + sqlWhere);
			rs.next();
			consumesum = rs.getInt(1);
			servnum = rs.getInt(2);
			rs.close();
			rs = stmt.executeQuery("select a.cid,a.cname,a.ctype,a.servnum,a.consume,a.district,c.sid,c.commuid,c.caddress,c.cmobile,c.ctel from (" +
				sql + ") as a, customer c where a.cid=c.cid");
			while(rs.next()) {
				sb = new StringBuffer();
				sb.append("\t");
				sb.append(rs.getString(1)).append(",");
				sb.append(rs.getString(2).trim()).append(",");
				sb.append(bundle.getProperty("ctype" + rs.getString(3))).append(",");
				sb.append(rs.getString(4)).append(",");
				sb.append(rs.getString(5)).append(",");
				sb.append(bundle.getProperty("district" + rs.getString(6))).append(",");
				sb.append(allStreet.getProperty(rs.getString(7))).append(",");
				sb.append(allCommunity.getProperty(rs.getString(8))).append(",");
				sb.append(rs.getString(9)).append(",");
				sb.append(rs.getString(10) + (rs.getString(11) == null? "" : " " + rs.getString(11)));
				out.println(sb.toString());
			}
			out.println();
			out.println();
			out.print(",,,");
			out.print(bundle.getProperty("servnum") + servnum);
			out.print(",");
			out.println(bundle.getProperty("consumesum") + consumesum);
			out.flush();
			out.close();
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(IOException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

	}

	public static boolean isAllocing() {
		boolean retval = true;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select count(*) from allocinfo where state='0'");
			rs.next();
			int r = rs.getInt(1);
			if (r == 0)
				retval = false;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public static void logFlow(Connection conn,Task task, Customer customer, int payment, int cash) throws SQLException {
		String strdate = DATE_FORMAT.format(new Date());
		int sum = customer.getCursum();
		PreparedStatement ps = null;

		try {
		    ps = conn.prepareStatement(
				"insert into accflow(" +
				"cid,cname,ctype,taskid,csex,cservice,district,sid,commuid,oldsum," +
				"cursum,consume,cash,wname,comid,recdate) " +
				"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");  //log the flow
			ps.setString(1, task.getCid());
			ps.setString(2, task.getCname());
			ps.setString(3, task.getCtype());
			ps.setString(4, task.getTaskid());
			ps.setString(5, task.getCsex());
			ps.setString(6, task.getCservice());
			ps.setString(7, customer.getDistrict());
			ps.setString(8, customer.getSid());
			ps.setString(9, customer.getCommuid());
			ps.setInt(10, sum);
			ps.setInt(11, sum - payment);
			ps.setInt(12, payment);
			ps.setInt(13, cash);
			ps.setString(14, task.getSname());
			ps.setString(15, task.getComid());
			ps.setString(16, strdate);
			ps.executeUpdate();
		}
		catch (SQLException ex) {
			throw ex;
		}
		finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
		}
	}

	public static JSONArray getAllocInfo() {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		JSONArray sa = new JSONArray();

		try {
			conn = openConnection();
			st = conn.createStatement();
			rs = st.executeQuery("select month,sum(shilinum),sum(shilimoney),sum(tinglinum),sum(tinglimoney),sum(yanyunum),sum(yanyumoney)," +
				"sum(zhitinum),sum(zhitimoney),sum(zhilinum),sum(zhilimoney),sum(jingshennum),sum(jingshenmoney),sum(duochongnum),sum(duochongmoney)," +
				"sum(totalnum),sum(totalmoney) from allocinfo group by month order by month desc");
			while (rs.next()) {
				JSONObject so = new JSONObject();
				so.put("id", rs.getString(1));
				String[] ss = new String[17];
				for (int i = 0; i < 17; i++)
					ss[i] = rs.getString(i + 1);
				so.put("cell", ss);
				sa.put(so);
			}
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (st != null) { try { st.close();	} catch (SQLException e) { ; }
				st = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return sa;
	}

	public static JSONArray getAllocSubInfo(String month) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		JSONArray sa = new JSONArray();

		try {
			conn = openConnection();
			ps = conn.prepareStatement("select district,shilinum,shilimoney,tinglinum,tinglimoney,yanyunum,yanyumoney," +
				"zhitinum,zhitimoney,zhilinum,zhilimoney,jingshennum,jingshenmoney,duochongnnum,duochongmoney," +
				"totalnum,totalmoney,state from allocinfo where month=? and district!='0'");
			ps.setString(1, month);
			rs = ps.executeQuery();
			while (rs.next()) {
				JSONObject so = new JSONObject();
				so.put("id", rs.getString(1));
				String[] ss = new String[18];
				for (int i = 0; i < 18; i++)
					ss[i] = rs.getString(i + 1);
				so.put("cell", ss);
				sa.put(so);
			}
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return sa;
	}

	public static void getAllocInfoExcel(HttpServletResponse response) {
		PrintWriter out;
		Properties bundle = new Properties();
		JSONArray sa = getAllocInfo();

		response.reset();
		response.setHeader("Content-Disposition", "attachment; filename=" + System.currentTimeMillis() + ".csv");
		response.setContentType("application/vnd.ms-excel; charset=GBK");
		try {
			out = response.getWriter();
			bundle.load(Customer.class.getResourceAsStream("Customer.properties"));
			for (int i = 1; i <= 9; i++) {
				out.print(bundle.getProperty("alloccol" + i));
				if (i != 8)
					out.print(",");
			}
			out.println("");
			for (int i = 0; i < sa.length(); i++) {
				JSONObject so = (JSONObject)sa.get(i);
				String[] ss = (String[])so.get("cell");
				StringBuffer sb = new StringBuffer();
				for (int j = 0; j <= 8; j++) {
					sb.append(ss[j]);
					if (j != 8)
						sb.append(",");
				}
				out.println(sb.toString());
			}
			out.flush();
			out.close();
		} catch(Exception e2) {
  			logger.error(e2.getMessage(), e2);
  		}
	}

	public static JSONArray getAllocDistrictInfo(String district) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		JSONArray sa = new JSONArray();

		try {
			conn = openConnection();
			ps = conn.prepareStatement("select month,district,shilinum,shilimoney,tinglinum,tinglimoney,yanyunum,yanyumoney," +
				"zhitinum,zhitimoney,zhilinum,zhilimoney,jingshennum,jingshenmoney,duochongnum,duochongmoney," +
				"totalnum,totalmoney,state from allocinfo where district=? order by month desc");
			ps.setString(1, district);
			rs = ps.executeQuery();
			while (rs.next()) {
				JSONObject so = new JSONObject();
				so.put("id", rs.getString(1));
				String[] ss = new String[19];
				for (int i = 0; i < 19; i++)
					ss[i] = rs.getString(i + 1);
				so.put("cell", ss);
				sa.put(so);
			}
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return sa;
	}

	public static void getAllocDistrictInfoExcel(String district, HttpServletResponse response) {
		PrintWriter out;
		Properties bundle = new Properties();
		JSONArray sa = getAllocDistrictInfo(district);

		response.reset();
		response.setHeader("Content-Disposition", "attachment; filename=" + System.currentTimeMillis() + ".csv");
		response.setContentType("application/vnd.ms-excel; charset=GBK");
		try {
			out = response.getWriter();
			bundle.load(Customer.class.getResourceAsStream("Customer.properties"));
			out.print(bundle.getProperty("alloccol1"));
			out.print(",");
			out.print(bundle.getProperty("coldistrict"));
			for (int i = 2; i <= 17; i++) {
				out.print(",");
				out.print(bundle.getProperty("alloccol" + i));
			}
			out.println("");
			for (int i = 0; i < sa.length(); i++) {
				JSONObject so = (JSONObject)sa.get(i);
				String[] ss = (String[])so.get("cell");
				StringBuffer sb = new StringBuffer();
				sb.append(ss[0]).append(",");
				sb.append(bundle.getProperty("district" + ss[1]));
				for (int j = 2; j <= 17; j++) {
					sb.append(",");
					sb.append(ss[j]);
				}
				out.println(sb.toString());
			}
			out.flush();
			out.close();
		} catch(Exception e2) {
  			logger.error(e2.getMessage(), e2);
  		}
	}

	public static JSONArray getAllGovuser(String district) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		JSONArray sa = new JSONArray();
		String sql = "select * from govuser";
		if ((district != null) && !district.equals("0"))
			sql += " where district='" + district + "'";
		
		sql += " order by uid";

		try {
			conn = openConnection();
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				JSONObject so = new JSONObject();
				so.put("id", rs.getString("uid"));
				String[] ss = new String[5];
				for (int i = 0; i < 5; i++)
					ss[i] = rs.getString(i + 1).trim();
				so.put("cell", ss);
				sa.put(so);
			}
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (st != null) { try { st.close();	} catch (SQLException e) { ; }
				st = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return sa;
	}

	public static int govuserAdd(String uid, String uname, String upass, String district, String sid) {
		int retval = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("insert into govuser(uid,uname,upass,district,sid) values(?,?,?,?,?)");
			ps.setString(1, uid);
			ps.setString(2, uname);
			ps.setString(3, upass);
			ps.setString(4, district);
			ps.setString(5, sid);
			if (ps.executeUpdate() == 1)
				retval = 0;
		} catch(SQLException e) {
			retval = e.getErrorCode();
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public static boolean govuserEdit(String uid, String uname, String upass, String district, String sid) {
		boolean retval = false;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("update govuser set uname=?,upass=?,district=?,sid=? where uid=?");
			ps.setString(1, uname);
			ps.setString(2, upass);
			ps.setString(3, district);
			ps.setString(4, sid);
			ps.setString(5, uid);
			ps.executeUpdate();
			retval = true;
			logger.info("A gov user is edited: " + uid);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public static boolean govuserDel(String uid) {
		if (DBUtil.executeUpdate("delete from govuser where uid='" + uid + "'") == 1) {
			logger.info("A gov user is deleted: " + uid);
			return true;
		} else
			return false;
	}

	public static Hashtable govUserLogin(String uid, String upass) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Hashtable<String, String> hu = new Hashtable<String, String>();

		try {
			conn = openConnection();
			ps = conn.prepareStatement("select * from govuser where uid=? and upass=?");
			ps.setString(1, uid);
			ps.setString(2, upass);
			rs = ps.executeQuery();
			if (rs.next()) {
				hu.put("uname", rs.getString("uname").trim());
				hu.put("udistrict", rs.getString("district"));
				hu.put("usid", rs.getString("sid").trim());
			}
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return hu;
	}

	public static JSONArray xgUserAll() {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		JSONArray sa = new JSONArray();

		try {
			conn = openConnection();
			st = conn.createStatement();
			rs = st.executeQuery("select * from xguser");
			while (rs.next()) {
				JSONObject so = new JSONObject();
				so.put("id", rs.getString("uid"));
				String[] ss = new String[4];
				for (int i = 0; i < 4; i++)
					ss[i] = rs.getString(i + 1).trim();
				so.put("cell", ss);
				sa.put(so);
			}
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (st != null) { try { st.close();	} catch (SQLException e) { ; }
				st = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return sa;
	}

	public static int xgUserAdd(String uid, String uname, String upass, String prof) {
		int retval = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("insert into xguser(uid,uname,upass,prof) values(?,?,?,?)");
			ps.setString(1, uid);
			ps.setString(2, uname);
			ps.setString(3, upass);
			ps.setString(4, prof);
			if (ps.executeUpdate() == 1)
				retval = 0;
		} catch(SQLException e) {
			retval = e.getErrorCode();
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public static int xgUserEdit(String uid, String uname, String upass, String prof) {
		int retval = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("update xguser set uname=?,upass=?,prof=? where uid=?");
			ps.setString(1, uname);
			ps.setString(2, upass);
			ps.setString(3, prof);
			ps.setString(4, uid);
			ps.executeUpdate();
			retval = 0;
			logger.info("A xg user is edited: " + uid);
		} catch(SQLException e) {
			retval = e.getErrorCode();
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public static boolean xgUserDel(String uid) {
		if (DBUtil.executeUpdate("delete from xguser where uid='" + uid + "'") == 1) {
			logger.info("A xg user is deleted: " + uid);
			return true;
		} else
			return false;
	}

	public static Hashtable xgUserLogin(String uid, String upass) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Hashtable<String, String> hu = new Hashtable<String, String>();

		try {
			conn = openConnection();
			ps = conn.prepareStatement("select * from xguser where uid=? and upass=?");
			ps.setString(1, uid);
			ps.setString(2, upass);
			rs = ps.executeQuery();
			if (rs.next()) {
				hu.put("uname", rs.getString("uname").trim());
				hu.put("uprof", rs.getString("prof"));
			}
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return hu;
	}

	public static int changePass(String udis, String uid, String newPass) {
		String tbname = udis != null? "govuser":"xguser";
		int rownum = DBUtil.executeUpdate("update " + tbname + " set upass='" + newPass + "' where uid='" + uid + "'");
		
		if (rownum == 1)
			logger.info("A user changed password: " + uid);

		return rownum;
	}

	public static String buildSqlWhere(Hashtable params) {
		if ((params == null) || params.isEmpty())
			return "";

		String sqlWhere = " where 1=1";
		Iterator ir = params.keySet().iterator();
		while(ir.hasNext()) {
			String k = (String)ir.next();
			String v = (String)params.get(k);

			if ((v.charAt(0) == '[') && (v.charAt(v.length() - 1)) == ']') {
				int p = v.indexOf(',');
				String v1 = v.substring(1, p);
				String v2 = v.substring(p + 1, v.length() - 1);
				sqlWhere += " and " + k + ">='" + v1 + "' and " + k + "<='" + v2 + "'";
			} else if ((v.charAt(0) == '<') && (v.charAt(v.length() - 1)) == '>') {
				if (v.length() > 2)
					sqlWhere += " and " + k + " like '%" + v.substring(1, v.length() - 1) + "%'";
			} else if (v.charAt(0) == '<') {
				sqlWhere += " and " + k + "<=" + v.substring(1, v.length());
			} else if (v.charAt(0) == '>') {
				sqlWhere += " and " + k + ">=" + v.substring(1, v.length());
			} else {
				sqlWhere += " and " + k + "='" + v + "'";
			}
		}

		logger.debug(sqlWhere);
		return sqlWhere;
	}

	public static Properties getAllStreet() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		Properties streets = new Properties();

		try {
			conn = BaseServlet.openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select sid,sname from street order by sid");
			while (rs.next())
				streets.setProperty(rs.getString(1), rs.getString(2));
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return streets;
	}
	
	public static Properties getAllStreetByDistrict(String district) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		Properties streets = new Properties();

		try {
			conn = BaseServlet.openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select sid,sname from street where district='" + district + "' order by sid");
			while (rs.next())
				streets.setProperty(rs.getString(1), rs.getString(2));
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return streets;
	}
	
	public static int netMapAdd(String nid, String nname, String pname) {
		int retval = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("insert into netmap(nid,nname,pname,px,py) values(?,?,?,'','')");
			ps.setString(1, nid);
			ps.setString(2, nname);
			ps.setString(3, pname);
			if (ps.executeUpdate() == 1)
				retval = 0;
		} catch(SQLException e) {
			retval = e.getErrorCode();
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}
	
	public static int netMapSetPosition(String nid, String px, String py) {
		int retval = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("update netmap set px=?,py=? where nid=?");
			ps.setString(1, px);
			ps.setString(2, py);
			ps.setString(3, nid);
			retval = ps.executeUpdate();
		} catch(SQLException e) {
			retval = e.getErrorCode();
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public static Properties getAllCommunity() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		Properties communities = new Properties();

		try {
			conn = BaseServlet.openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select cid,cname from community order by cid");
			while (rs.next())
				communities.setProperty(rs.getString(1), rs.getString(2));
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return communities;
	}
	
	public static JSONArray getAllNetMap() {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		JSONArray sa = new JSONArray();

		try {
			conn = openConnection();
			st = conn.createStatement();
			rs = st.executeQuery("select nid,nname,pname,px,py from netmap");
			while (rs.next()) {
				JSONObject so = new JSONObject();
				so.put("id", rs.getString("nid"));
				String[] ss = new String[4];
				for (int i = 0; i < 4; i++)
					ss[i] = rs.getString(i + 1).trim();
				so.put("cell", ss);
				sa.put(so);
			}
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (st != null) { try { st.close();	} catch (SQLException e) { ; }
				st = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return sa;
	}

	public static void createDB() throws SQLException{
		Connection conn = null;
		Statement stmt = null;

	  conn = DriverManager.getConnection(DBURL + ";create=true");
	  ResourceBundle bundle = ResourceBundle.getBundle("yiguan.createdb");
	  Enumeration e = bundle.getKeys();
		stmt = conn.createStatement();
		while(e.hasMoreElements()) {
			String sql = bundle.getString((String)e.nextElement());
			stmt.executeUpdate(sql);
		}
		stmt.close();
		conn.close();
	}
	
	public static int executeUpdate(String sql) {
		Connection conn = null;
		Statement stmt = null;
		int r = 0;
		
		try {
			conn = openConnection();
			stmt = conn.createStatement();
			r = stmt.executeUpdate(sql);
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
		
		return r;
	}

	public static Connection openConnection() throws SQLException {
		Connection conn = null;
		conn = DriverManager.getConnection(DBURL);
		return conn;
	}
}